package com.gyh.modules.test;

import java.io.FileOutputStream;
import java.text.ParseException;
import java.util.Date;

import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.gyh.modules.excel.ExcelException;
import com.gyh.modules.excel.ExcelUtils;

/**
* @author 作者 gyh:
* @version 创建时间：2018年1月7日 下午7:59:36
* 
*/
public class AppTest23 {

	public static void main(String[] args) {
		String path = "C:/Users/Administrator/Desktop/mm.xlsx";
		XSSFWorkbook wb = null;
		XSSFSheet sheet = null;
		try {
			wb = ExcelUtils.getWorkbook(path);
			sheet = ExcelUtils.getSheet(wb, 0);
			for(int i = 1; i <= sheet.getLastRowNum(); i ++) {
	            XSSFRow row = sheet.getRow(i);
	            if (ExcelUtils.isRowEmpty(row)) break;
	            
	            String cell0 = ExcelUtils.cellToString(row.getCell(0));
	            double cell1 = ExcelUtils.cellToNumber(row.getCell(1));
	            String cell2 = ExcelUtils.cellToString(row.getCell(2));
	            double cell3 = ExcelUtils.cellToNumber(row.getCell(3));
	            int cell4 = (int) ExcelUtils.cellToNumber(row.getCell(4));
	            Date cell5 = ExcelUtils.cellToDate(row.getCell(5));
	            String cell6 = ExcelUtils.cellToString(row.getCell(0));
	            
	            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
	              dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
	            CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
	            XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
	              dvConstraint, addressList);
	            validation.setShowErrorBox(true);
	            sheet.addValidationData(validation);
			}
		} catch (ExcelException e) {
			FileOutputStream outputStream = null;
	        try {
	            outputStream = new FileOutputStream("C:/Users/Administrator/Desktop/nn.xlsx");
	            System.out.println(e.getMessage());
	            sheet.getRow(e.getRow()).getCell(e.getColumn()).setCellComment(ExcelUtils.getComment(wb, sheet, e.getMessage()));
	            wb.write(outputStream);
	            outputStream.close();
	            wb.close();
	        } catch (Exception e1) {
	            e1.printStackTrace();
	        }
		} catch ( ParseException e) {
			e.printStackTrace();
		}
		
		
	}
}
